﻿<%@ Page Language="C#" %>
<%@ Import Namespace="IRL.Utilities" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">    
    
    int totalProp = 0;
    decimal propTotalValue = 0, projTotalValue = 0;
    enum OPS { Proposal, Project };
    int selectedYear;
    int market = 0;
    
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.Cookies["office"] != null)
        {
            market = Convert.ToInt32(Request.Cookies["office"].Value);
        }
        else
        {
            if (Page.Cache["market"] == null)
            {
                market = 1;
            }
            else
            {
                market = Convert.ToInt32(Page.Cache["market"].ToString());
            }
        }

        if (Page.Cache["year"] == null)
        {
            selectedYear = DateTime.Today.Year;
        }
        else
        {
            selectedYear = Convert.ToInt32(Page.Cache["year"].ToString());
        }
        
        //if (!Page.IsPostBack)
        //{
            loadData();
            Project_Count();
            Proposal_Summary();
            Project_Summary();
        //}
    }

    void loadData()
    {
        if (Request.Cookies["office"] != null)
        {
            optMarket.Visible = false;
        }
        else
        {
            if (optMarket.SelectedValue != "")
                market = Convert.ToInt32(optMarket.SelectedValue);
            object connStr = ConfigurationSettings.AppSettings["sqlConn"];
            using (SqlConnection conn = new SqlConnection(connStr.ToString()))
            {
                SqlCommand cmd = new SqlCommand("SELECT [id], [Name] FROM tbl_IRLs;", conn);
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                optMarket.DataTextField = "Name";
                optMarket.DataValueField = "id";
                // bind to control
                optMarket.DataSource = dr;
                optMarket.DataBind();
                // set default
                optMarket.SelectedValue = market.ToString();
            }            
        }
        // year
        if (cboYear.SelectedValue != "")
            selectedYear = Convert.ToInt32(cboYear.SelectedValue);
        if (cboYear.Items.Count == 0)
        {
            for (int i = DateTime.Today.Year; i >= 2000; i--)
            {
                cboYear.Items.Add(i.ToString());
            }
        }
        cboYear.SelectedValue = selectedYear.ToString();
    }
    string GetMarketName(int id)
    {
        string __value = "";
        object connStr = ConfigurationSettings.AppSettings["sqlConn"];
        using (SqlConnection conn = new SqlConnection(connStr.ToString()))
        {
            SqlCommand cmd = new SqlCommand("SELECT [Name] FROM tbl_IRLs WHERE [id]=" + id, conn);
            conn.Open();
            try
            {
                __value = (string)cmd.ExecuteScalar();
            }
            catch (Exception e) { __value = ""; }
        }
        return __value;
    }

    void Project_Summary()
    {
        projTotalValue = SummaryGenerate(OPS.Project);
        object ConnStr = ConfigurationSettings.AppSettings["sqlConn"];
        using (SqlConnection conn = new SqlConnection(ConnStr.ToString()))
        {
            string SqlStr = "SELECT p.* , h.[Value] AS [HitRate] FROM ProjectSummary p INNER JOIN HitRate h ON p.[Type]=h.[Name];";
            SqlCommand cmd = new SqlCommand(SqlStr, conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            rptProjSummary.DataSource = dr;
            rptProjSummary.DataBind();
        }
    }
    void Proposal_Summary()
    {
        propTotalValue = SummaryGenerate(OPS.Proposal);
        object ConnStr = ConfigurationSettings.AppSettings["sqlConn"];
        using (SqlConnection conn = new SqlConnection(ConnStr.ToString()))
        {
            string SqlStr = "SELECT * FROM ProposalSummary;";
            SqlCommand cmd = new SqlCommand(SqlStr, conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            rptPropSummary.DataSource = dr;
            rptPropSummary.DataBind();
        }
    }
    decimal SummaryGenerate(OPS Type)
    {
        decimal __value;
        object ConnStr = ConfigurationSettings.AppSettings["sqlConn"];
        try
        {
            using (SqlConnection conn = new SqlConnection(ConnStr.ToString()))
            {
                string SqlStr = "";
                if (Type == OPS.Proposal)
                {
                    SqlStr = "sp_Gen_Proposal_Summary";
                }
                else
                {
                    SqlStr = "sp_Gen_Project_Summary";
                }
                SqlCommand cmd = new SqlCommand(SqlStr, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@market", SqlDbType.Int).Value = market;
                cmd.Parameters.Add("@foryear", SqlDbType.Int).Value = selectedYear;
                conn.Open();
                __value = (decimal)cmd.ExecuteScalar();
            }
        }
        catch (Exception e) { __value = 0; }        

        return __value;
    }
    

    void Project_Count()
    {
        object ConnStr = ConfigurationSettings.AppSettings["sqlConn"];
        using (SqlConnection conn = new SqlConnection(ConnStr.ToString()))
        {
            string SqlStr = "SELECT [Status], COUNT(*) AS [Proj] FROM cms_Proposals WHERE [ForYear]="+ selectedYear +" AND [Market]="+ market +" GROUP BY [Status];";
            SqlCommand cmd = new SqlCommand(SqlStr, conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            rptProps.DataSource = dr;
            rptProps.DataBind();
        }
    }
    protected void rptProps_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
        int prop = Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "Proj"));
        totalProp += prop;
    }

    protected void optMarket_SelectedIndexChanged(object sender, EventArgs e)
    {
        market = Convert.ToInt32(optMarket.SelectedValue);
        Page.Cache["market"] = market;
    }

    protected void cboYear_SelectedIndexChanged(object sender, EventArgs e)
    {
        selectedYear = Convert.ToInt32(cboYear.SelectedValue);
        Page.Cache["year"] = selectedYear;
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">

<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

    <title>Indochina Research</title>
    <link rel="SHORTCUT ICON" href="../irl.ico" type="image/x-icon" />
    <link type="text/css" rel="stylesheet" href="../css/global.css" />
    <link type="text/css" rel="stylesheet" href="css/cms.css" />
	

</head>

<body>
<table width="773" border="0" cellspacing="0" cellpadding="0" align="center">
	<tr>
		<td width="765" height="540" align="center" valign="top" style="border:solid 1px #10147e;" >
			<!-- header -->
			<table border="0" cellpadding="0" cellspacing="0" width="100%">
				<tr bgcolor="#F4F4F9">
					<td colspan="3" align="right" style="padding:10px;">
						<b class="blueL">Client Management System</b>
					</td>
				</tr>
				<tr>
					<td class="wave_l">&nbsp;</td>
					<td><img src="images/wave_logo.jpg" width="107" height="83"></td>
					<td class="wave_r">&nbsp;</td>
				</tr>
				<tr>
					<td colspan="3" style="padding:0 20px;">
						<div style="background-color:#bcb3d5; padding:5px 10px;">
							<span style="float:right">
							    &nbsp;
							</span>
							<span class="pathmap">
								<a href="default.aspx" >CPanel</a> :: 
								<a href="default2.aspx" >Ops Files</a> :: 
								Proposals &amp; Confirmed Projects (Summary)
						    </span>
						</div>
						<br />
						
						
					</td>
				</tr>
				
			</table>
			
			<!-- body -->
			<form id="DataForm" method="post" runat="server">
			<table border="0" width="100%" cellspacing="0" cellpadding="0">
				<tr>
					<td align="left" valign="top" style="padding:0 20px;">
						
						<!-- Summary Data -->
						<table border="0" cellspacing="0" cellpadding="0">
						    <tr>
						        <td width="45%">
						            <div>Year: <asp:DropDownList ID="cboYear" AutoPostBack="true" runat="server" OnSelectedIndexChanged="cboYear_SelectedIndexChanged" /></div>
						            <div><asp:RadioButtonList ID="optMarket" RepeatColumns="4" RepeatLayout="table" CellPadding="8" CellSpacing="0" runat="server" AutoPostBack="true" OnSelectedIndexChanged="optMarket_SelectedIndexChanged" /></div>
						            
						            <h3><%=GetMarketName(market) %> <%=selectedYear.ToString() %> Proposals &amp; Confirmed Projects Summary</h3><br clear="all" />
						            <span><a class="edit" href="ops_Proposals.aspx">&raquo; View Details</a></span></td>
						        <td width="10%">&nbsp;</td>
						        <td width="45%" valign="top">
						            <asp:Repeater ID="rptProps" runat="server" OnItemDataBound="rptProps_ItemDataBound">
						                <HeaderTemplate>
						                    <table class="data" border="0" cellspacing="0" cellpadding="4">
						                        <tr class="header">
						                            <td width="180">Project</td>						                
						                            <td>#</td>
						                        </tr>
						                </HeaderTemplate>
            						    
						                <ItemTemplate>
						                        <tr>
						                            <td><%# DataBinder.Eval(Container.DataItem, "Status") %></td>
						                            <td align="center"><b><%# DataBinder.Eval(Container.DataItem, "Proj") %></b></td>
						                        </tr>
						                </ItemTemplate>
            						    
						                <FooterTemplate>
						                        <tr class="footer">
						                            <td>Total</td>
						                            <td><%=totalProp %></td>
						                        </tr>
						                    </table>
						                </FooterTemplate>
						            </asp:Repeater>
						        </td>
						    </tr>
						    						    						    
						    <tr height="24">
						        <td>&nbsp;</td>
						        <td>&nbsp;</td>
						        <td>&nbsp;</td>
						    </tr>
						    <tr>
						        <td><b class="fldNote">Proposals</b></td>
						        <td>&nbsp;</td>
						        <td><b class="fldNote">Confirmed Projects</b></td>
						    </tr>
						    <tr>
						        <td valign="top">
						            <asp:Repeater ID="rptPropSummary" runat="server">
						                <HeaderTemplate>
						                    <table class="data" border="0" cellspacing="0" cellpadding="4">
						                        <tr class="header">
						                            <td>Type</td>
						                            <td># Prop</td>
						                            <td>Value</td>
						                            <td>Mth Av</td>
						                            <td>Avg Value</td>
						                        </tr>
						                </HeaderTemplate>
            						    
						                <ItemTemplate>
						                        <tr>
						                            <td><%# DataBinder.Eval(Container.DataItem, "Type") %></td>
						                            <td align="center"><%# DataBinder.Eval(Container.DataItem, "Prop") %></td>
						                            <td><%# String.Format("{0:$#,#0}",DataBinder.Eval(Container.DataItem, "Value")) %></td>
						                            <td align="center"><%# String.Format("{0:#%}", DataBinder.Eval(Container.DataItem, "MthAv")) %></td>
						                            <td align="right"><%# String.Format("{0:$#,#0}", DataBinder.Eval(Container.DataItem, "AvgValue")) %></td>
						                        </tr>
						                </ItemTemplate>
            						    
						                <FooterTemplate>
						                        <tr class="footer">
						                            <td colspan="4">Total Value</td>
						                            <td align="right"><%=String.Format("{0:$#,#0}", propTotalValue) %></td>
						                        </tr>
						                    </table>
						                </FooterTemplate>
						            </asp:Repeater>
						        </td>
						        
						        <td>&nbsp;</td>
						        
						        <td valign="top">
						            <asp:Repeater ID="rptProjSummary" runat="server">
						                <HeaderTemplate>
						                    <table class="data" border="0" cellspacing="0" cellpadding="4">
						                        <tr class="header">
						                            <td>Type</td>
						                            <td># Proj</td>
						                            <td>Value</td>
						                            <td>Mth Av</td>
						                            <td>Avg Value</td>
						                            <td>Hit Rate</td>
						                        </tr>
						                </HeaderTemplate>
            						    
						                <ItemTemplate>
						                        <tr>
						                            <td><%# DataBinder.Eval(Container.DataItem, "Type") %></td>
						                            <td align="center"><%# DataBinder.Eval(Container.DataItem, "Proj") %></td>
						                            <td><%# String.Format("{0:$#,#0}",DataBinder.Eval(Container.DataItem, "Value")) %></td>
						                            <td align="center"><%# String.Format("{0:#%}", DataBinder.Eval(Container.DataItem, "MthAv")) %></td>
						                            <td align="right"><%# String.Format("{0:$#,#0}", DataBinder.Eval(Container.DataItem, "AvgValue")) %></td>
						                            <td align="center"><%# String.Format("{0:#%}", DataBinder.Eval(Container.DataItem, "HitRate")) %></td>
						                        </tr>
						                </ItemTemplate>
            						    
						                <FooterTemplate>
						                        <tr class="footer">
						                            <td colspan="5">Total Value</td>
						                            <td align="right"><%=String.Format("{0:$#,#0}", projTotalValue) %></td>
						                        </tr>
						                    </table>
						                </FooterTemplate>
						            </asp:Repeater>
						        </td>
						    </tr>
						</table>
						
						
					</td>
				</tr>
			</table>
			</form>
			
		</td>
		
		<td width="6" class="rightShadow" valign="top">
			<img src="images/white.gif" width="6" height="6" /></td>
	</tr>
	
	<!-- bottom shadow -->
	<tr>
		<td height="6" class="bottomShadow" >
			<img src="images/white.gif" width="6" height="6" /></td>
		
		<td width="6" height="6">
			<img src="images/shadow_corner.gif" width="6" height="6" /></td>
	</tr>
</table>


</body>
</html>
